package com.ncmem.up7;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.google.gson.Gson;
import com.ncmem.up7.model.FileInf;
import com.ncmem.up7.sql.SqlExec;
import com.ncmem.up7.sql.SqlWhereMerge;


/*
 * 原型
 */
public class DBFile {

    public DBFile()
    {
    }

    public static boolean existSameFile(String n,String pid,Integer uid)
    {
        SqlWhereMerge swm = new SqlWhereMerge();
        swm.equal("f_nameLoc", n);
        swm.equal("f_pid", pid);
        swm.equal("f_deleted", 0);
        swm.equal("f_uid", uid);

        SqlExec se = new SqlExec();
        return se.count("up7_files", swm.to_sql())>0;

    }

    /**
     * 清空文件表，文件夹表数据。
     */
    public void Clear()
    {
        DbHelper db = new DbHelper();
        db.ExecuteNonQuery("delete from up7_files");
        db.ExecuteNonQuery("delete from up7_folders");
    }

    public void merged(String id)
    {
        String sql = "update up7_files set f_lenSvr=f_lenLoc,f_perSvr='100%',f_merged=1 where f_id=?";
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);

        try
        {
            cmd.setString(1,id);
        } catch(SQLException e){
            e.printStackTrace();
        }
        db.ExecuteNonQuery(cmd);
    }

    public void add(FileInf inf)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into up7_files(");
        sb.append(" f_id");//1
        sb.append(",f_fdTask");//2
        sb.append(",f_uid");//3
        sb.append(",f_nameLoc");//4
        sb.append(",f_nameSvr");//5
        sb.append(",f_pathLoc");//6
        sb.append(",f_pathSvr");//7
        sb.append(",f_pathRel");//8
        sb.append(",f_blockCount");//9
        sb.append(",f_blockSize");//10
        sb.append(",f_blockPath");//11
        sb.append(",f_lenLoc");//12
        sb.append(",f_sizeLoc");//13
        sb.append(",f_pid");//14
        sb.append(",f_fdChild");//15

        sb.append(") values(");

        sb.append(" ?");//sb.append("@f_id");
        sb.append(",?");//sb.append(",@f_fdTask");
        sb.append(",?");//sb.append(",@f_uid");
        sb.append(",?");//sb.append(",@f_nameLoc");
        sb.append(",?");//sb.append(",@f_nameSvr");
        sb.append(",?");//sb.append(",@f_pathLoc");
        sb.append(",?");//sb.append(",@f_pathSvr");
        sb.append(",?");//sb.append(",@f_pathRel");
        sb.append(",?");//sb.append(",@f_blockCount");
        sb.append(",?");//sb.append(",@f_blockSize");
        sb.append(",?");//sb.append(",@f_blockPath");
        sb.append(",?");//sb.append(",@f_lenLoc");
        sb.append(",?");//sb.append(",@f_sizeLoc");
        sb.append(",?");//sb.append(",@f_pid");
        sb.append(",?");//sb.append(",@f_fdChild");
        sb.append(")");

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sb.toString());
        try {
            cmd.setString (1, inf.id);
            cmd.setBoolean(2, inf.fdTask);
            cmd.setInt    (3, inf.uid);
            cmd.setString (4, inf.nameLoc);
            cmd.setString (5, inf.nameSvr);
            cmd.setString (6, inf.pathLoc);
            cmd.setString (7, inf.pathSvr);
            cmd.setString (8, inf.pathRel);
            cmd.setInt    (9, inf.blockCount);
            cmd.setInt    (10,inf.blockSize);
            cmd.setString (11,inf.blockPath);
            cmd.setLong   (12,inf.lenLoc);
            cmd.setString (13,inf.sizeLoc);
            cmd.setString(14, inf.pid);
            cmd.setBoolean(15, inf.f_fdChild);
        } catch (SQLException e) {e.printStackTrace();}

        db.ExecuteNonQuery(cmd);
    }

    public void complete(String id)
    {
        String sql = "update up7_files set f_complete=1 where f_id=?";
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        try {
            cmd.setString(1, id);
        } catch (SQLException e) {e.printStackTrace();}

        db.ExecuteNonQuery(cmd);
    }

    public boolean read(FileInf m)
    {
        boolean ret = false;
        StringBuilder sb = new StringBuilder();
        sb.append("select");
        sb.append(" f_pathSvr");//1
        sb.append(",f_pathRel");//2
        sb.append(",f_md5");//3
        sb.append(",f_lenLoc");//4
        sb.append(",f_sizeLoc");//5
        sb.append(",f_pos");//6
        sb.append(",f_blockCount");//7
        sb.append(",f_blockSize");//8
        sb.append(",f_blockPath");//9
        sb.append(",f_lenSvr");//10
        sb.append(",f_pid");//11
        sb.append(",f_perSvr");//12
        sb.append(",f_complete");//13
        sb.append(",f_time");//14
        sb.append(",f_deleted");//15
        sb.append(",f_merged");//16
        sb.append(",f_pidRoot");//17
        sb.append(",f_fdTask");//18
        sb.append(",f_fdChild");//19
        sb.append(",f_uid");//20
        sb.append(",f_nameLoc");//21
        sb.append(",f_nameSvr");//22
        sb.append(",f_pathLoc");//23
        sb.append(" from up7_files where f_id=?");

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sb.toString());
        try {
            cmd.setString(1, m.id);
            ResultSet r = db.ExecuteDataSet(cmd);
            if(r.next())
            {
                m.pathSvr 		= r.getString(1);
                m.pathRel 		= r.getString(2);
                m.md5			= r.getString(3);
                m.lenLoc		= r.getLong(4);
                m.sizeLoc		= r.getString(5);
                m.blockCount 	= r.getInt(7);
                m.blockSize  	= r.getInt(8);
                m.blockPath  	= r.getString(9);
                m.lenSvr     	= r.getLong(10);
                m.pid        	= r.getString(11);
                m.perSvr     	= r.getString(12);
                m.complete   	= r.getBoolean(13);
                m.PostedTime    = r.getDate(14);
                m.deleted    	= r.getBoolean(15);
                //m.merged   	= r.getBoolean(16);
                m.pidRoot    	= r.getString(17);
                m.fdTask     	= r.getBoolean(18);
                m.f_fdChild  	= r.getBoolean(19);
                m.uid        	= r.getInt(20);
                m.nameLoc    	= r.getString(21);
                m.nameSvr    	= r.getString(22);
                m.pathLoc    	= r.getString(23);
                ret = true;
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {e.printStackTrace();}
        return ret;
    }

    public void remove(String id,Integer uid)
    {
        String sql = "update up7_files set f_deleted=1 where f_id=? and f_uid=?";
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        try {
            cmd.setString(1, id);
            cmd.setInt(2, uid);
        } catch (SQLException e) {e.printStackTrace();}

        db.ExecuteNonQuery(cmd);
    }

    public String all_uncmp(int uid)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(" f_id");
        sb.append(",f_fdTask");
        sb.append(",f_nameLoc");
        sb.append(",f_pathLoc");
        sb.append(",f_pathSvr");
        sb.append(",f_blockPath");
        sb.append(",f_sizeLoc");
        sb.append(",f_perSvr");
        sb.append(" from up7_files");
        sb.append(" where f_uid=? and f_deleted=0 and f_complete=0");

        ArrayList<FileInf> files = new ArrayList<FileInf>();
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sb.toString());
        try {
            cmd.setInt(1, uid);
            ResultSet r = db.ExecuteDataSet(cmd);
            while(r.next())
            {
                FileInf f 		= new FileInf();
                f.id 			= r.getString(1);
                f.fdTask 		= r.getBoolean(2);
                f.nameLoc 		= r.getString(3);
                f.pathLoc 		= r.getString(4);
                f.pathSvr 		= r.getString(5);
                f.blockPath 	= r.getString(6);
                f.sizeLoc 		= r.getString(7);
                f.perSvr 		= r.getString(8);
                files.add(f);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        if(files.size() < 1) return "";
        Gson g = new Gson();
        return g.toJson( files );
    }

    public void process(String id,String perSvr,long lenSvr)
    {
        String sql = "update up7_files set f_perSvr=?,f_lenSvr=? where f_id=?";
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        try {
            cmd.setString(1, perSvr);
            cmd.setLong(2, lenSvr);
            cmd.setString(3, id);
        } catch (SQLException e) {e.printStackTrace();}

        db.ExecuteNonQuery(cmd);
    }

    public static boolean existSameFile(String n,String pid)
    {
        SqlWhereMerge swm = new SqlWhereMerge();
        swm.equal("f_nameLoc", n.trim());
        swm.equal("f_pid", pid.trim(), false);
        swm.equal("f_deleted", 0);

        SqlExec se = new SqlExec();
        return se.count("up7_files", swm.to_sql())>0;
    }

    /**
     * 更新子文件路径
     * @param pathRelOld
     * @param pathRelNew
     */
    public void updatePathRel(String pathRelOld,String pathRelNew)
    {
        //更新子文件路径
        String sql = String.format("update up7_files set f_pathRel=REPLACE(f_pathRel,'%s/','%s/') where CHARINDEX('%s/',f_pathRel)>0",
                pathRelOld,
                pathRelNew,
                pathRelOld
        );

        SqlExec se = new SqlExec();
        se.exec(sql);

    }
}